Differences between NVL, NVL2 and COALESCE

NVL and NVL2 are Oracle specific and where introduced before the ANSI-92 standard (which includes COALESCE) was created. NVL and COALESCE are nearly identical, even there are some differenced I would like to point out:

NVL always executes both arguments

NVL always evaluates the first and second arguments and is therefore a bit slower than COALESCE. For example SELECT NVL(1,longFunctionCall()) FROM DUAL will execute the first and second argument, even if it is clear, that the second argument will not be used. Despite COALESCE will execute the first argument (which is 1 and not NULL) and will finish there.

COALESCE sometimes executes the second argument

Mostly, COALESCE just executes the first argument, but there are exceptions event if the first argument is different NULL. One case is the sequence NEXTVAL. You can test phenomenon when using CURVAL, to see, that the sequence has changed.

NVL do an implicit conversation of the datatype

One big advantage of NVL is the implicit conversation of the datatype, meaning that the datatype of the first parameter counts. Therefore the following statement will work: SELECT NVL(‘String’, SYSDATE) FROM DUAL; It will work, because the Date Format can be converted to a Character Datatype. COALESCE on the other hand will throw an “inconsistent data type” error.

COALESCE accepts multiple arguments

While NVL just has 2 arguments, COALESCE takes multiple arguments. On the other hand COALESCE has no NVL2 functionality (if x is not NULL then y, else z). An example with multiple arguments is the following:

SELECT COALESCE(NULL, NULL, 1, 'Hi') 
FROM DUAL;
-- ORA-00932 "inconsistent datatypes"

SELECT COALESCE(NULL, NULL, 'Hi', 1) 
FROM DUAL;
-- ORA-00932 "inconsistent datatypes"

SELECT COALESCE(NULL, NULL, 'Hi', TO_CHAR(1)) 
FROM DUAL;
-- Result: Hi